package com.lvkerry.generate;

import com.lvkerry.generate.bean.*;


import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;

/**
 * @author Kerry on 18/03/07
 */

public class GenerateMainService {

    private Connection connect = null;
    private Set<UserBean> users = new LinkedHashSet<>();
    private List<MenuBean> menus = new ArrayList<>();
    private Set<UserDeskBean> userDesks = new LinkedHashSet<>();

    public static void main(String[] args) {

        GenerateMainService main = new GenerateMainService();
        main.connect();

        main.truncate();

        main.addUser(22);
        main.queryUsers();

        main.addUserDesk(4);
        main.queryUserDesk();

        main.addMenu();
        main.queryMenus();


        main.addOrderData();

        //main.addUserToken();
    }

    private void addUserToken() {

        //language=SQL
        String sql = "INSERT INTO oauth_client_details (client_id, resource_ids, client_secret, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove)\n" +
                "VALUES ('oauth_client_1', 'resource_id', 'oauth_client_1_secret_key', 'all', 'client_credentials,refresh_token', NULL,\n" +
                "                          'client', 86400, 691200, NULL, NULL),\n" +
                "  ('oauth_client_2', 'resource_id', 'oauth_client_2_secret_key', 'all', 'password,refresh_token', NULL, 'client', 86400,\n" +
                "                     691200, NULL, NULL)";
        System.out.println("开始添加授权................");
        System.out.println(sql);
        try {
            Statement statement = connect.createStatement();
            boolean execute = statement.execute(sql);
            connect.commit();
            System.out.println("开始添加授权................Ok........");

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void truncate() {
        System.out.println("清空原来数据库......");
        //language=SQL
        String sql = "TRUNCATE TABLE t_order";
        truncateTable(sql);
        sql = "TRUNCATE TABLE t_menu";
        truncateTable(sql);
        sql = "TRUNCATE TABLE t_user_desk";
        truncateTable(sql);
        sql = "TRUNCATE TABLE t_user";
        truncateTable(sql);
        sql = "TRUNCATE TABLE oauth_client_details";
        //truncateTable(sql);
        System.out.println("清空原来数据库...........ok");
    }

    private void truncateTable(String sql) {
        try {
            Statement statement = connect.createStatement();
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    private void queryUserDesk() {
        System.out.println("查询对应关系.......");
        //language=SQL
        String sql = "SELECT * FROM t_user_desk";

        try {
            Statement statement = connect.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                Long userId = resultSet.getLong("user_id");
                Integer tableNo = resultSet.getInt("table_no");
                UserDeskBean userDeskBean = new UserDeskBean(userId, tableNo);
                this.userDesks.add(userDeskBean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("查询对应关系.......ok....");
    }


    private void addOrderData() {

        System.out.println("添加订单数据.........................");
        OrderGenete orderGenete = new OrderGenete(this.users, this.menus, this.userDesks);

        Set<OrderBean> orderBeans = orderGenete.addOrderData(40);

        //language=SQL
        String addSql = "INSERT INTO t_order(lunch_date, menu_avatar_url, menu_id, menu_name, name, order_no, order_status, order_time, price, remark, table_no, user_id, username) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            connect.setAutoCommit(false);
            PreparedStatement statement = connect.prepareStatement(addSql);
            int page = 0;
            for (OrderBean bean : orderBeans) {
                statement.setString(1, bean.getLunchDate());
                statement.setString(2, bean.getMenuAvatarUrl());
                statement.setLong(3, bean.getMenuId());
                statement.setString(4, bean.getMenuName());
                statement.setString(5, bean.getName());
                statement.setString(6, bean.getOrderNo());
                statement.setInt(7, bean.getOrderStatus());
                statement.setLong(8, bean.getOrderTime());
                statement.setBigDecimal(9, bean.getPrice());
                statement.setString(10, bean.getRemark());
                statement.setInt(11, bean.getTableNo());
                statement.setLong(12, bean.getUserId());
                statement.setString(13, bean.getUsername());
                statement.addBatch();
                page++;
                if (page > 100) {
                    statement.executeBatch();
                    connect.commit();
                    page = 0;
                }
            }
            statement.executeBatch();
            connect.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("添加订单数据.........................");
    }

    private void addMenu() {
        Set<MenuBean> menuBeans = MenuGenete.addMenuData(12, 12);

        System.out.println("开始添加菜单....");

        //language=SQL
        String menuSql = "INSERT INTO t_menu(add_time, avatar_url, desp, is_deleted, name, price, remark) VALUES (?,?,?,?,?,?,?)";

        try {

            connect.setAutoCommit(false);

            PreparedStatement preparedStatement = connect.prepareStatement(menuSql);
            for (MenuBean bean : menuBeans) {

                preparedStatement.setLong(1, bean.getAddTime());
                preparedStatement.setString(2, bean.getAvatarUrl());
                preparedStatement.setString(3, bean.getDesp());
                preparedStatement.setInt(4, bean.getIsDeleted());
                preparedStatement.setString(5, bean.getName());
                preparedStatement.setBigDecimal(6, bean.getPrice());
                preparedStatement.setString(7, bean.getRemark());
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            connect.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("添加菜单完成..........ok");
    }


    private void queryMenus() {
        System.out.println("查询所有菜单.......................");
        //language=SQL
        String queryMenuSql = "SELECT * FROM t_menu";
        try {
            Statement statement = connect.createStatement();
            ResultSet resultSet = statement.executeQuery(queryMenuSql);
            while (resultSet.next()) {

                Long menuId = resultSet.getLong("menu_id");
                Long addTime = resultSet.getLong("add_time");
                String avatarUrl = resultSet.getString("avatar_url");
                String desp = resultSet.getString("desp");
                Integer isDeleted = resultSet.getInt("is_deleted");
                String name = resultSet.getString("name");
                BigDecimal price = resultSet.getBigDecimal("price");
                String remark = resultSet.getString("remark");
                MenuBean mb = new MenuBean();
                mb.setMenuId(menuId);
                mb.setAddTime(addTime);
                mb.setAvatarUrl(avatarUrl);
                mb.setDesp(desp);
                mb.setIsDeleted(isDeleted);
                mb.setName(name);
                mb.setPrice(price);
                mb.setRemark(remark);
                menus.add(mb);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("查询所有菜单.......................OK");
    }


    private void queryUsers() {
        System.out.println("查询所有用户...");
        //language=SQL
        String queryUser = "SELECT * FROM t_user WHERE user_role='USER'";
        Statement statement = null;
        try {
            statement = connect.createStatement();
            ResultSet resultSet = statement.executeQuery(queryUser);

            while (resultSet.next()) {
                Long userId = resultSet.getLong("user_id");
                String avatarUrl = resultSet.getString("avatar_url");
                Integer isDeleted = resultSet.getInt("is_deleted");
                String name = resultSet.getString("name");
                String phoneNumber = resultSet.getString("phone_number");
                String username = resultSet.getString("username");

                UserBean userBean = new UserBean();
                userBean.setUserId(userId);
                userBean.setAvatarUrl(avatarUrl);
                userBean.setIsDeleted(isDeleted);
                userBean.setName(name);
                userBean.setPhoneNumber(phoneNumber);
                userBean.setUsername(username);

                users.add(userBean);

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("查询所有用户完成........ok...");
    }

    private void addUserDesk(int userDesk) {
        System.out.println("开始添加对应关系.....");

        try {
            int index = 0;
            Set<UserDeskBean> uubs = new LinkedHashSet<>();
            for (UserBean user : users) {
                UserDeskBean uub = new UserDeskBean(user.getUserId(), 1 + (index / userDesk));
                uubs.add(uub);
                index++;
            }

            //language=SQL
            String addUdSQL = "INSERT INTO t_user_desk(user_id, table_no) VALUES (?,?)";
            connect.setAutoCommit(false);
            PreparedStatement preparedStatement = connect.prepareStatement(addUdSQL);
            for (UserDeskBean bean : uubs) {
                preparedStatement.setLong(1, bean.getUserId());
                preparedStatement.setLong(2, bean.getTableNo());
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            connect.commit();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("添加对应关系完成..........ok");

    }


    private void connect() {
        Runtime.getRuntime().addShutdownHook(new Thread(() -> {
            if (connect != null) {
                try {
                    connect.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }));

        Driver driver = null;

        try {
            driver = new com.mysql.jdbc.Driver();

        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(-1);
        }
        Properties properties = new Properties();
        try {
            properties.load(new BufferedInputStream(new FileInputStream("config/config.properties")));
        } catch (IOException e) {
            e.printStackTrace();
            System.exit(-1);
        }

        properties.setProperty("user", properties.getProperty("jdbc.username"));
        properties.setProperty("password", properties.getProperty("jdbc.passwd"));
        try {
            connect = driver.connect(properties.getProperty("jdbc.url"), properties);
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(-1);
        }
        System.out.println("连接数据库成功.....");


    }

    private void addUser(int count) {
        System.out.println("开始添加用户..." + count);

        Set<NameBean> nameBeans = UserName.generateUsername(count);

        //language=SQL
        String addUserSQL = "INSERT INTO t_user(add_time, avatar_url, is_deleted, name, password, user_role, username) VALUES (?,?,?,?,?,?,?)";

        try {
            connect.setAutoCommit(false);
            PreparedStatement statement = connect.prepareStatement(addUserSQL);


            //add admin & boss

            statement.setLong(1, System.currentTimeMillis());
            statement.setString(2, "http://taobao.gson8.com/1.jpg");
            statement.setInt(3, 0);
            statement.setString(4, "管理员");
            statement.setString(5, Password.PW_USER);
            statement.setString(6, "ADMIN");
            statement.setString(7, "admin");
            statement.addBatch();

            statement.setLong(1, System.currentTimeMillis());
            statement.setString(2, "http://taobao.gson8.com/1.jpg");
            statement.setInt(3, 0);
            statement.setString(4, "老板");
            statement.setString(5, Password.PW_USER);
            statement.setString(6, "BOSS");
            statement.setString(7, "boss");
            statement.addBatch();

            statement.setLong(1, System.currentTimeMillis());
            statement.setString(2, "http://taobao.gson8.com/1.jpg");
            statement.setInt(3, 0);
            statement.setString(4, "一般用户-测试");
            statement.setString(5, Password.PW_USER);
            statement.setString(6, "USER");
            statement.setString(7, "user");
            statement.addBatch();


            statement.executeBatch();
            connect.commit();

            // user
            for (NameBean bean : nameBeans) {
                statement.setLong(1, System.currentTimeMillis());
                statement.setString(2, "http://taobao.gson8.com/1.jpg");
                statement.setInt(3, 0);
                statement.setString(4, bean.getChineseName());
                statement.setString(5, Password.PW_USER);
                statement.setString(6, "USER");
                statement.setString(7, bean.getEnglishName());
                statement.addBatch();
            }
            statement.executeBatch();
            connect.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("添加用户完成.....ok");
    }
}
